package com.example.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 数据库连接工具类
 * 使用HikariCP连接池管理数据库连接
 */
public class DatabaseUtil {
    private static final String DB_URL = "jdbc:sqlite:/home/ubuntu/orders_system.db";
    private static Connection connection;
    
    static {
        try {
            // 加载SQLite JDBC驱动
            Class.forName("org.sqlite.JDBC");
            // 初始化数据库连接
            initializeDatabase();
            System.out.println("[DatabaseUtil] 数据库连接池初始化成功");
        } catch (Exception e) {
            System.err.println("[DatabaseUtil] 数据库连接池初始化失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    private static void initializeDatabase() throws SQLException {
        connection = DriverManager.getConnection(DB_URL);
        
        // 创建必要的表
        try (Statement stmt = connection.createStatement()) {
            // 创建用户表
            stmt.execute("CREATE TABLE IF NOT EXISTS users (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "username TEXT UNIQUE NOT NULL," +
                        "password TEXT NOT NULL," +
                        "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
            
            // 创建订单表
            stmt.execute("CREATE TABLE IF NOT EXISTS orders (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "category TEXT NOT NULL," +
                        "product TEXT NOT NULL," +
                        "user_id TEXT NOT NULL," +
                        "quantity INTEGER NOT NULL," +
                        "order_time TIMESTAMP NOT NULL," +
                        "rating REAL," +
                        "is_valid BOOLEAN DEFAULT 1," +
                        "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
            
            // 创建统计表
            stmt.execute("CREATE TABLE IF NOT EXISTS order_valid_stats (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "valid_orders INTEGER DEFAULT 0," +
                        "invalid_orders INTEGER DEFAULT 0," +
                        "total_orders INTEGER DEFAULT 0," +
                        "updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
            
            stmt.execute("CREATE TABLE IF NOT EXISTS category_order_stats (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "category TEXT NOT NULL," +
                        "order_count INTEGER DEFAULT 0," +
                        "updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
            
            stmt.execute("CREATE TABLE IF NOT EXISTS product_order_stats (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "product TEXT NOT NULL," +
                        "order_count INTEGER DEFAULT 0," +
                        "updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
            
            // 创建推荐表
            stmt.execute("CREATE TABLE IF NOT EXISTS user_recommendations (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "user_id TEXT NOT NULL," +
                        "recommended_product TEXT NOT NULL," +
                        "score REAL NOT NULL," +
                        "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
            
            stmt.execute("CREATE TABLE IF NOT EXISTS item_recommendations (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "product TEXT NOT NULL," +
                        "similar_product TEXT NOT NULL," +
                        "similarity_score REAL NOT NULL," +
                        "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
            
            // 插入默认用户（如果不存在）
            stmt.execute("INSERT OR IGNORE INTO users (username, password) VALUES " +
                        "('admin', 'admin123'), " +
                        "('user1', 'user123'), " +
                        "('user2', 'user123')");
            
            System.out.println("[DatabaseUtil] 数据库表初始化完成");
        }
    }
    
    /**
     * 获取数据库连接
     * @return Connection 数据库连接
     * @throws SQLException SQL异常
     */
    public static Connection getConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            System.out.println("[DatabaseUtil] 重新建立数据库连接");
            connection = DriverManager.getConnection(DB_URL);
        }
        return connection;
    }
    
    /**
     * 关闭数据库连接
     */
    public static void closeConnection() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
                System.out.println("[DatabaseUtil] 数据库连接已关闭");
            }
        } catch (SQLException e) {
            System.err.println("[DatabaseUtil] 关闭数据库连接时出错: " + e.getMessage());
        }
    }
    
    /**
     * 测试数据库连接
     * @return boolean 连接是否成功
     */
    public static boolean testConnection() {
        try (Connection conn = getConnection()) {
            return conn != null && !conn.isClosed();
        } catch (SQLException e) {
            System.err.println("数据库连接测试失败: " + e.getMessage());
            return false;
        }
    }
} 